﻿

using DataDriven;
using DataService.Common;
using DataService.Core.Common;
using DataService.Core.DataHelper;
using DataService.Core.Log;
using DataService.Core.ServiceDTO;
using DataService.Extensions;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Xml.Linq;

namespace DataService.DataAccessLayer
{
    public class SqlServer : RemoteDB
    {
        public override DatabaseType DBType => DatabaseType.SqlServer;
       

        private static int _exchangeLock = 0;


        public override int Insert<T>(IList<T> dataList, string suffix, DbConnection conn, DbTransaction tran)
        {
            if (dataList == null || dataList.Count == 0)
                return 0;
            SqlConnection sqlConn = conn as SqlConnection;
            SqlTransaction sqlTran = null;
            if (tran != null)
            {
                sqlTran = tran as SqlTransaction;
            }

            var dic = BatchHelper.ReturnBeatch<T>(dataList, 5000);
            try
            {
                InsertToDataBase<T>(suffix, sqlConn, sqlTran, dic);
                return dataList.Count;
            }
            catch (Exception ex)
            {
                throw ex;
            }


        }
        private void InsertToDataBase<T>(string stuff, SqlConnection sqlConn, SqlTransaction sqlTran, Dictionary<int, List<T>> dic)
        {
            var properties = typeof(T).GetProperties();
            using (SqlBulkCopy sbc = new SqlBulkCopy(sqlConn, SqlBulkCopyOptions.Default, sqlTran))
            {
                sbc.BatchSize = 5000;
                sbc.BulkCopyTimeout = 60;
                sbc.DestinationTableName = typeof(T).Name + stuff;
                for (int i = 0; i < properties.Length; i++)
                {
                    sbc.ColumnMappings.Add(properties[i].Name, properties[i].Name);
                }
                foreach (var item in dic)
                {
                    DataTable dataTable = DataTableHelper.ListToTable<T>(item.Value);
                    sbc.WriteToServer(dataTable);
                }
            }
        }

        public override int BeatchUpdate<T>(IEnumerable<T> t, string suffix, DbConnection conn, DbTransaction tran)
        {
            if (t == null || t.Count() == 0)
            {
                return 0;
            }
            //优化时，决定是否改成SqlBulkCopy批量更新
            List<string> pkFields = GetModelPkFields<T>();

            string sql = DBSchemaSqlExtensions.GetUpdateSql<T>(DBType, pkFields, typeof(T).Name + suffix);
            return MapperService.ExecuteInternal(conn, sql, t, tran, null, null, ParameterPrefix);
        }
        public override int BeatchUpdate<T>(IEnumerable<T> t, Expression<Func<T, object>> fieldFunc, string suffix, DbConnection conn, DbTransaction tran)
        {
            if (t == null || t.Count() == 0)
            {
                return 0;
            }
            //优化时，决定是否改成SqlBulkCopy批量更新
            List<string> pkFields = GetModelPkFields<T>();


            string sql = DBSchemaSqlExtensions.GetUpdateSql<T>(DBType, pkFields, fieldFunc, typeof(T).Name + suffix);
            return MapperService.ExecuteInternal(conn, sql, t, tran, null, null, ParameterPrefix);
        }



        #region 分页查询
        protected override string SetTotalRowsSql(string tableName, string dataBaseName, string schema)
        {
            string sql = $"select rows from sysindexes where id = object_id('{tableName}') and indid in (0,1)";
            return sql;
        }
        protected override string SetStratRowSql(string pkIdName, string tableName, string where)
        {
            string sql = $" select {pkIdName} from {tableName} where {where} Order By {pkIdName} asc offset 0 rows fetch next 1 rows only;";
            return sql;
        }
        protected override string SetEndRowSql(string pkIdName, string tableName, string where)
        {
            string sql = $" select {pkIdName} from {tableName} where {where} Order By {pkIdName} desc offset 0 rows fetch next 1 rows only;";
            return sql;
        }

        protected override string SetForwardPageSql(string sql, string where, string pkIdName, long pkValue, int dataNum)
        {
            string doSql = $"{sql} where {pkIdName}>{pkValue} and {where} order by {pkIdName} asc offset 0 rows fetch next {dataNum} rows only;";
            return doSql;
        }
        protected override string SetReversePageSql(string sql, string where, string pkIdName, long pkValue, int dataNum)
        {
            string doSql = $"{sql} where {pkIdName}<{pkValue} and {where} order by {pkIdName} desc offset 0 rows fetch next {dataNum} rows only;";
            return doSql;
        }

        #endregion


        private String ColumnSchemaSql
        {
         

            get
            {
                var sb = new StringBuilder();
                sb.Append("SELECT ");
                sb.Append("TableName=d.name,");
                sb.Append("ColOrder=Convert(Int,a.colorder),");
                sb.Append("ColName=a.name,");
                sb.Append("[Identity]=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then Convert(Bit,1) else Convert(Bit,0) end,");
                sb.Append("IsPK=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (");
                sb.Append("SELECT name FROM sysindexes WHERE id = a.id AND indid in(");
                sb.Append("SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid");
                sb.Append("))) then Convert(Bit,1) else Convert(Bit,0) end,");
                sb.Append("ColType=b.name,");
                sb.Append("ColLength= Convert(Int,a.length),");
                sb.Append("ValueLength=COLUMNPROPERTY(a.id,a.name,'PRECISION'),");
                sb.Append("ScaleLength=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),");
                sb.Append("IsNullable=case when a.isnullable=1 then Convert(Bit,1)else Convert(Bit,0) end,");
                sb.Append("DefaultValue=isnull(e.text,''),");
                sb.Append("FieldDes=isnull(g.[value],'')");
                sb.Append("FROM syscolumns a ");
                sb.Append("left join systypes b on a.xtype=b.xusertype ");
                sb.Append("inner join sysobjects d on a.id=d.id  and d.xtype='U' ");
                sb.Append("left join syscomments e on a.cdefault=e.id ");
                //if (IsSQL2005)
                //{
                sb.Append("left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id and g.name = 'MS_Description'  ");
                //}
                //else
                //{
                //    sb.Append("left join sysproperties g on a.id=g.id and a.colid=g.smallid  ");
                //}
                // sb.Append("order by a.id,a.colorder");
                return sb.ToString();
            }

        }

        private string IndexSql
        {
            // //select COLUMN_NAME,t.TABLE_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS i LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t ON t.CONSTRAINT_NAME=i.CONSTRAINT_NAME WHERE t.CONSTRAINT_TYPE='PRIMARY KEY'
            get
            {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.Append("SELECT ");
                stringBuilder.Append("TableName = O.Name,");
                stringBuilder.Append("IndexId = ISNULL(KC.[object_id], IDX.index_id),");
                stringBuilder.Append("IndexName = IDX.Name,");
                stringBuilder.Append("IndexType = ISNULL(KC.type_desc, 'Index'),");
                stringBuilder.Append("ColumnName = C.Name,");
                stringBuilder.Append("Sort = CASE INDEXKEY_PROPERTY(IDXC.[object_id], IDXC.index_id, IDXC.index_column_id, 'IsDescending') WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,");
                stringBuilder.Append("PrimaryKey = IDX.is_primary_key,");
                stringBuilder.Append("[UQIQUE]= IDX.is_unique,");
                stringBuilder.Append("Type_Desc=IDX.type_desc ");
                stringBuilder.Append("FROM sys.indexes IDX ");
                stringBuilder.Append("INNER JOIN sys.index_columns IDXC ON IDX.[object_id]= IDXC.[object_id] AND IDX.index_id = IDXC.index_id ");
                stringBuilder.Append("LEFT JOIN sys.key_constraints KC ON IDX.[object_id]= KC.[parent_object_id] AND IDX.index_id = KC.unique_index_id ");

                stringBuilder.Append("INNER JOIN sys.objects O ON O.[object_id]= IDX.[object_id] ");
                stringBuilder.Append("INNER JOIN sys.columns C ON O.[object_id]= C.[object_id] AND O.type = 'U' AND O.is_ms_shipped = 0 AND IDXC.Column_id = C.Column_id");
                return stringBuilder.ToString();
            }
        }
        private static readonly Dictionary<Type, String[]> _DataTypes = new Dictionary<Type, String[]>()
        {
            { typeof(Byte[]), new String[] { "binary({0})", "image", "varbinary({0})", "timestamp" } },
            //{ typeof(DateTimeOffset), new String[] { "datetimeoffset({0})" } },
            { typeof(Guid), new String[] { "uniqueidentifier" } },
            //{ typeof(Object), new String[] { "sql_variant" } },
            //{ typeof(TimeSpan), new String[] { "time({0})" } },
            { typeof(Boolean), new String[] { "bit" } },
            { typeof(Byte), new String[] { "tinyint" } },
            { typeof(Int16), new String[] { "smallint" } },
            { typeof(Int32), new String[] { "int" } },
            { typeof(Int64), new String[] { "bigint" } },
            { typeof(Single), new String[] { "real" } },
            { typeof(Double), new String[] { "float" } },
            { typeof(Decimal), new String[] { "money", "decimal({0}, {1})", "numeric({0}, {1})", "smallmoney" } },
            { typeof(DateTime), new String[] { "datetime", "smalldatetime", "datetime2({0})", "date" } },
            { typeof(String), new String[] { "nvarchar({0})", "ntext", "text", "varchar({0})", "char({0})", "nchar({0})", "xml" } }
        };

        public override List<string> FindAllTableNames(DbConnection conn, DbTransaction tran)
        {
            string sql = "select Name from sysobjects where xtype='U'";
            var list = MapperService.QueryInternal<string>(conn, sql, null, tran, null, null, ParameterPrefix).ToList();
            return list;
        }

        public override List<ModelColumns> FindAllColumns(DbConnection conn)
        {
            List<ModelColumns> columns = MapperService.QueryInternal<ModelColumns>(conn, ColumnSchemaSql, null, null, null, null, ParameterPrefix).ToList();
            foreach (var item in columns)
            {
                if (item.ColType.ToLower() == "uniqueidentifier")
                {
                    item.ValueLength = 36;
                }
            }
            return columns;
        }

        public override List<ModelIndexs> FindAllIndexs(DbConnection conn)
        {
            List<ModelIndexs> indexs = MapperService.QueryInternal<ModelIndexs>(conn, IndexSql, null, null, null, null, ParameterPrefix).ToList() ?? new List<ModelIndexs>();
            return indexs;
        }

        public override Tables FindAllModelTableSchema(List<string> tableNames, DbConnection conn)
        {
            StringBuilder stringBuilder = new StringBuilder();
            stringBuilder.AppendLine(ColumnSchemaSql);
            Dictionary<string, object> dic = new Dictionary<string, object>();
            stringBuilder.Append(" WHERE d.name IN(");
            for (int i = 0; i < tableNames.Count; i++)
            {
                stringBuilder.Append($"@name{i}").Append(",");
                dic[$"name{i}"] = tableNames[i];
            }
            stringBuilder.Length--;
            stringBuilder.Append(")");


            StringBuilder indexBuilder = new StringBuilder();
            indexBuilder.AppendLine(IndexSql);
            Dictionary<string, object> indexDic = new Dictionary<string, object>();
            indexBuilder.Append(" WHERE O.Name IN(");
            for (int i = 0; i < tableNames.Count; i++)
            {
                indexBuilder.Append($"@name{i}").Append(",");
                indexDic[$"name{i}"] = tableNames[i];
            }
            indexBuilder.Length--;
            indexBuilder.Append(")");

            List<ModelColumns> columns = MapperService.QueryInternal<ModelColumns>(conn, stringBuilder.ToString(), dic, null, null, null, ParameterPrefix).ToList();
            foreach (var item in columns)
            {
                if (item.ColType.ToLower() == "uniqueidentifier")
                {
                    item.ValueLength = 36;
                }
            }
            List<ModelIndexs> indexs = MapperService.QueryInternal<ModelIndexs>(conn, indexBuilder.ToString(), indexDic, null, null, null, ParameterPrefix).ToList() ?? new List<ModelIndexs>();
            Tables tables = new Tables()
            {
                Columns = columns,
                Indexs = indexs
            };
            return tables;
        }
        public override string GetIsExistsTableSql(string databaseName, string schemaName)
        {
            string sql = "select Count(1) from sysobjects where xtype='U' AND Name=@Name";
            return sql;
        }
       

        public override bool CreateDataBaseSchema(string dbFileDir, string databaseName, DbConnection conn)
        {
            if (Interlocked.Exchange(ref _exchangeLock, 1) == 0)
            {
                string sql = "select COUNT(*) from master..sysdatabases where name=@name";
                Dictionary<string, object> dic = new Dictionary<string, object>();
                dic["name"] = databaseName;
                int count = MapperService.ScalarInternal<int>(conn, sql, dic, null, null, null, ParameterPrefix);
                if (count > 0)
                {
                    TraceLog.WriteLine($"{databaseName}已经存在", "创建架构");
                    return true;
                }
                //优化时定时任务提前把第二天的库、表创建完成
                //string tempDataBaseFilePath = @"D:\DataBaseBack";
                if (!Directory.Exists(dbFileDir))
                {
                    Directory.CreateDirectory(dbFileDir);
                }
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.AppendFormat("CREATE DATABASE {0} ON PRIMARY ", databaseName);
                stringBuilder.AppendFormat("(NAME = {0}, ", databaseName);
                stringBuilder.AppendFormat("FILENAME = '{1}\\{0}.mdf', ", databaseName, dbFileDir);
                //stringBuilder.Append("SIZE = 10MB, MAXSIZE = 2048MB, FILEGROWTH = 10%)");
                stringBuilder.Append("SIZE = 10MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%)"); //不限制最大大小
                stringBuilder.AppendFormat("LOG ON (NAME = {0}_Log, ", databaseName);
                stringBuilder.AppendFormat("FILENAME = '{1}\\{0}.ldf', ", databaseName, dbFileDir);
                stringBuilder.Append("SIZE = 8MB, ");
                stringBuilder.Append("MAXSIZE = UNLIMITED, ");
                stringBuilder.Append("FILEGROWTH = 10%)");

                List<string> tableSchemaTexts = new List<string>();
                tableSchemaTexts.Add(stringBuilder.ToString());
                MapperService.ExecuteSchemaSql(conn, null, tableSchemaTexts, ParameterPrefix);
                TraceLog.WriteLine($"{dbFileDir}\\{databaseName}创建完成", "创建架构");
                Interlocked.Exchange(ref _exchangeLock, 0);
                return true;
            }
            else
            {
                TraceLog.WriteLine($"{dbFileDir}{databaseName}未进入线程创建数据库", "创建架构");
                return false;
            }

        }

        public override bool CreatTable(Tables tables, DbConnection conn, DbTransaction tran)
        {
           TraceLog.WriteLine($"数据库【{conn.Database}】，开始执行创建表结构脚本", "Sqlserver创建表");
            //优化时定时任务提前把第二天的表创建完成
            List<ModelColumns> tableShemas = tables.Columns;
            List<ModelIndexs> tableIndexs = tables.Indexs;
            var tableSchemaTexts = CreateTableSql(tableShemas, tableIndexs);
            string str = string.Join("*", tableSchemaTexts);
            TraceLog.WriteLine(str, "脚本", "Sqlserver创建表");
            //此处需要优化
            int count = MapperService.ExecuteSchemaSql(conn, tran, tableSchemaTexts, ParameterPrefix);
            if (count > 0)
            {
                List<string> tempTableNames = new List<string>();
                int hashCode = ConnectionString.GetHashCode();
                if (_tablesFields.ContainsKey(hashCode))
                {
                    tempTableNames = _tablesFields[hashCode];
                    if (!tempTableNames.Any(x => x == tableShemas[0].FullTableName))
                    {
                        tempTableNames.Add(tableShemas[0].FullTableName);
                        _tablesFields[hashCode] = tempTableNames;
                        TraceLog.WriteLine($"创建表结构【CreatTable】：{tableShemas[0].FullTableName}，添加到缓存", $"{DBType}创建表");
                    }
                }
                else
                {
                    tempTableNames.Add(tableShemas[0].FullTableName);
                    _tablesFields[hashCode] = tempTableNames;
                }
            }



            return count > 0;
        }
        #region 创建架构
        private List<string> CreateTableSql(List<ModelColumns> tableSchemas, List<ModelIndexs> tableIndexs)
        {
            List<string> textSchemas = new List<string>();
            var tableNameList = tableSchemas.Select(x => x.FullTableName).Distinct().ToList();
            foreach (var itemTableName in tableNameList)
            {
                var colSchemaList = tableSchemas.Where(x => x.FullTableName == itemTableName).ToList();
                StringBuilder stringBuilder = new StringBuilder();

                string tableName = itemTableName;//分表后缀,前调用分表前处理
                //这里需要按照规则生成表名
                stringBuilder.Append($"CREATE TABLE {tableName}(");

                foreach (var itemCol in colSchemaList)
                {
                    stringBuilder.Append(itemCol.ColName).Append(" ");
                    stringBuilder.Append(GetColType(itemCol)).Append(" ");
                    stringBuilder.Append(GetColIsNull(itemCol)).Append(" ");
                    stringBuilder.Append(DefaultValue(itemCol)).Append(",");
                }
                stringBuilder.Length--;
                stringBuilder.AppendLine(");");

                //一个表只有一个聚集索引，这里默认主键是聚集索引
                //创建约束、创建索引、组合索引、创建联合主键、创建自增字段、
                //创建索引前需要先删除该索引；DROP INDEX 表名.索引名[,…]
                //如果这个表没有主键，就需要查找是否有聚集索引
                //
                //with (drop_existing = on|off)，加上这个的意思是如果这个索引还在表上就drop 掉然后在create 一个新的。特别是在聚集索引上使用这个就可以不会引起非聚集索引的重建
                //UNIQUE 表示创建的索引具有唯一性
                //CLUSTERED 指明创建的索引为聚集索引
                //NONCLUSTERED 指明创建的索引为非聚集索引
                //ASC、DESC 指定特定的索引列的排序方式为升序(默认)或降序
                List<ModelIndexs> itemTableIndexs = tableIndexs.Where(x => x.FullTableName == itemTableName).ToList();
                if (itemTableIndexs != null && itemTableIndexs.Count > 0)
                {
                    StringBuilder indexBuilder = GetIndexScheme(itemTableIndexs, itemTableName);
                    stringBuilder.Append(indexBuilder);
                }
                textSchemas.Add(stringBuilder.ToString());

            }

            return textSchemas;
        }

        protected override string GetColType(ModelColumns colDTO)
        {
            string tempColType = colDTO.ColType.ToLower();
            if (tempColType == "nvarchar" || tempColType == "varchar" || tempColType == "char" || tempColType == "nchar")
            {
                int tempLength = colDTO.ValueLength;
                if (tempLength == -1)
                {
                    return $"{tempColType}(max)";
                }
                return $"{tempColType}({tempLength})";
            }
            else if (tempColType == "binary" || tempColType == "varbinary")
            {
                int tempLength = colDTO.ValueLength;
                if (tempLength == -1)
                {
                    return $"{tempColType}(max)";
                }
                return $"{tempColType}({tempLength})";
            }
            else if (tempColType == "bit" || tempColType == "tinyint" || tempColType == "smallint" || tempColType == "int" || tempColType == "bigint")
            {
                return tempColType;
            }
            else if (tempColType == "uniqueidentifier")
            {
                return tempColType;
            }
            else if (tempColType == "datetime" || tempColType == "smalldatetime" || tempColType == "date" || tempColType == "time")
            {
                return tempColType;
            }
            else if (tempColType == "smallmoney" || tempColType == "money")
            {
                return tempColType;
            }
            else if (tempColType == "decimal" || tempColType == "numeric" || tempColType == "double" || tempColType == "float")
            {
                int tempLength = colDTO.ValueLength;
                int num = colDTO.ScaleLength;
                return $"{tempColType}({tempLength},{num})";
            }
            else
            {
                return tempColType;
            }
        }
        private string GetColIsNull(ModelColumns colDTO)
        {
            return colDTO.IsNullable ? "NULL" : "NOT NULL";
        }
        private string DefaultValue(ModelColumns colDTO)
        {
            if (string.IsNullOrEmpty(colDTO.DefaultValue))
            {
                return "";
            }
            return $"default{colDTO.DefaultValue}";
        }

        private StringBuilder GetIndexScheme(List<ModelIndexs> itemTableIndexs, string itemTableName)
        {
            //CREATE INDEX 语句中列的排序会影响查询的性能
            StringBuilder stringBuilder = new StringBuilder();
            var indexNames = itemTableIndexs.Select(x => x.IndexName).Distinct().ToList();
            int index = 0;
            foreach (string itemIndexName in indexNames)
            {
                index++;
                var tempIndexs = itemTableIndexs.Where(x => x.IndexName == itemIndexName).ToList();
                string colNames = string.Join(",", tempIndexs.Select(x => x.ColumnName));
                var itemTableIndexsDTO = tempIndexs.FirstOrDefault();

                //string tempIndexName = itemIndexName;
                //这里认为主键是聚集索引，正常逻辑，主键也应该是聚集索引
                if (itemTableIndexsDTO.PrimaryKey)
                {
                    //在建立聚集索引的时候在多个字段上建立聚集索引是没有任何意义的；因为聚集索引查找是根据建立索引的第一个字段来查找；这里索引索引排序取第一条排序方式
                    //string pkIndex = $"ALTER TABLE {itemTableName} ADD CONSTRAINT {item.IndexName} PRIMARY KEY CLUSTERED({item.ColumnName} {item.Sort}); ";
                    // string pkIndexSql = $"ALTER TABLE {itemTableName} ADD CONSTRAINT {itemTableIndexsDTO.IndexName} PRIMARY KEY({colNames} {itemTableIndexsDTO.Sort}); ";

                    string tempIndexName = $"PK_{itemTableName}_{index}";

                    string pkIndexSql = $"ALTER TABLE {itemTableName} ADD CONSTRAINT {tempIndexName} PRIMARY KEY({colNames}); ";
                    stringBuilder.Append(pkIndexSql);
                }
                else if (!itemTableIndexsDTO.PrimaryKey && itemTableIndexsDTO.Type_Desc == "CLUSTERED")
                {
                    string tempIndexName = $"CLUSTERED_INDEX_{itemTableName}_{index}";
                    //非主键的聚集索引
                    string tempSql = $"CREATE CLUSTERED INDEX {tempIndexName} ON {itemTableName}({colNames});";
                    stringBuilder.Append(tempSql);
                }
                else if (itemTableIndexsDTO.UQIQUE && !itemTableIndexsDTO.PrimaryKey)
                {
                    string tempIndexName = $"UNIQUE_INDEX_{itemTableName}_{index}";
                    //string tempSql="CREATE UNIQUE NONCLUSTERED INDEX sno_cl_ind ON TestIndexTable(sfzUniqueIndex_2);"
                    string tempSql = $"CREATE UNIQUE INDEX {tempIndexName} ON {itemTableName}({colNames});";
                    stringBuilder.Append(tempSql);
                }
                else
                {
                    string tempIndexName = $"INDEX_{itemTableName}_{index}";
                    string tempSql = $"CREATE INDEX {tempIndexName} ON {itemTableName}({colNames});";
                    stringBuilder.Append(tempSql);
                }
            }
            return stringBuilder;
        }
        #endregion


    }
}
